package mango.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class CopyData {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    
    private static final String SRC_URL = "jdbc:mysql://118.145.0.57:3306/game2010002?useUnicode=true&amp;characterEncoding=utf-8";
    
    private static final String DES_URL = "jdbc:mysql://192.168.0.188:3306/game2010002?useUnicode=true&amp;characterEncoding=utf-8";
    
    private static final String USER = "root";
    private static final String PWD = "9@me2U$";
    
    public static void main(String[] args) throws Exception {
        Class.forName(DRIVER);
        ArrayList<String[]> params = new ArrayList<String[]>();
        params.add(new String[]{"passport", "hello@gmail.com"});
        copyData("role", "roleid='28714804'", params);
        
//        copyData("family", "master='23807139'", new ArrayList<String[]>());
        System.out.println("copy end...");
    }
    
    public static final Connection getConnection(String url, String user, String pwd)
            throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

    public static final void copyData(String tableName, String wpart, ArrayList<String[]> params) throws SQLException {
        String sql = "select * from " + tableName + " where " + wpart;
        System.out.println(sql);
        Connection connSrc = getConnection(SRC_URL, USER, PWD);
        PreparedStatement psSrc = connSrc.prepareStatement(sql);
        ResultSet retSet = psSrc.executeQuery();
        ResultSetMetaData rsmd = retSet.getMetaData();
        StringBuilder fpart = new StringBuilder();
        StringBuilder pPart = new StringBuilder();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            fpart.append(rsmd.getColumnLabel(i)).append(",");
            pPart.append("?,");
        }
        fpart.delete(fpart.length() - 1, fpart.length());
        pPart.delete(pPart.length() - 1, pPart.length());
        StringBuilder sqlcopy = new StringBuilder();
        sqlcopy.append("insert into ").append(tableName).append("(").append(fpart).append(")")
                .append(" values(").append(pPart).append(")");
        Connection connDes = getConnection(DES_URL, USER, PWD);
        String del = "delete from " + tableName + " where " + wpart;
        System.out.println(connDes + " " + del);
        connDes.prepareStatement(del).execute();
//        connDes.commit();
        System.out.println(sqlcopy);
        PreparedStatement psDes = connDes.prepareStatement(sqlcopy.toString());
        while(retSet.next()) {
            for(int i = 1; i <= rsmd.getColumnCount(); i++) {
                String lb = rsmd.getColumnLabel(i);
                Object val = retSet.getObject(i);
                if (params != null) {
                    for (String[] p : params) {
                        if (lb.equalsIgnoreCase(p[0])) {
                            val = p[1];
                            break;
                        }
                    }
                }
                psDes.setObject(i, val);
            }
            psDes.addBatch();
        }
        psDes.executeBatch();
        connDes.close();
        connSrc.close();
    }
}
